﻿/******************************************************   
创建者: 梁家健
创建时间: 2017-03-21
修改:
修改时间: 
功能: 品类数据存储
 ******************************************************/

using com.nm.ApiServer;
using com.nm.ApiServer.Attributes;
using com.nm.Attributes;
using george.Data;
using NHibernateVertica;
using PlanAPI.model;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Linq;
using com.nm.library;
using com.nm.library.Helper;

namespace PlanAPI
{
    [Description("品类数据存储")]
    public class PLModelProcessHandler : BaseHandler
    {
        public VerticaDataHelper Helper { get; set; } = new VerticaDataHelper();

        enum AreaOrShop
        {
            Area = 0,
            Shop = 1,
        }

        enum MonthOrWeek
        {
            Month=0,
            Week=1,
        }

        const string TUIPIN = "000005";

        /// Writer：梁家健
        /// Create Date：2017-03-21
        [MustLogin]
        [Description("区域-月-品类标准金额占比列表")]
        [ReturnType(typeof(PaperData<IGrouping<string, PLModelProcessModel.ListModel>>))]
        public ApiResult GetPLModelProcessAreaMonthList([ParamDescription("商品区域")] string ParentAreaID = "",
            [ParamDescription("类别")] string KindID = "",[ParamDescription("系列")] string SeriesID = "", 
            [ParamDescription("商品层")] string MaterialProperty = "", 
            [ParamDescription("页码")] int Page = 1, [ParamDescription("页数")] int Size = 10)
        {
            try
            {
                if (string.IsNullOrEmpty(UserInfo.UserCompanyId))
                {
                    return Error("该用户不属于任何公司!");
                }
                if (string.IsNullOrEmpty(UserInfo.UserExtrSource))
                {
                    return Error("该用户所在公司没有任何数据源!");
                }
                var set = new PLPlanYMSetHandler().ReturnPLPlanYMSet();
                if (set == null) return Error("没有要修改企划的月份");
                var groupBy = @"group by pro.Yearv,pro.Monthv,pro.ParentAreaID,pro.KindID,pro.SeriesID,pro.MaterialProperty";
                var count = ReturnPLModelProcessCount(set.Yearv.Value, set.Monthv.Value, ParentAreaID, KindID, SeriesID, MaterialProperty, "", !Convert.ToBoolean(AreaOrShop.Area), Convert.ToInt32(MonthOrWeek.Month), groupBy);
                var result = PaperData<IGrouping<string, PLModelProcessModel.ListModel>>.Create(count, Size, Page);
                var list = ReturnPLModelProcessList(set.Yearv.Value, set.Monthv.Value, ParentAreaID, KindID, SeriesID, MaterialProperty, "", !Convert.ToBoolean(AreaOrShop.Area), Convert.ToInt32(MonthOrWeek.Month), Page, Size).GroupBy(
                            d =>
                                d.Yearv + "_" + d.Monthv + "_" + d.ParentAreaID + "_" + d.KindID + "_" + d.SeriesID +
                                "_" + d.MaterialProperty)
                        .ToList();
                result.Items = list;
                return Success(result);
            }
            catch (Exception ex)
            {
                return Error(ex.Message);
            }
        }

        /// Writer：梁家健
        /// Create Date：2017-03-21
        [MustLogin]
        [Description("区域-周-品类标准金额占比列表")]
        [ReturnType(typeof(PaperData<PLModelProcessModel.ListModel>))]
        public ApiResult GetPLModelProcessAreaWeekList([ParamDescription("企划年")] long Yearv = -1L,
            [ParamDescription("企划月")] long Monthv = -1L, [ParamDescription("商品区域")] string ParentAreaID = "",
            [ParamDescription("类别")] string KindID = "", [ParamDescription("系列")] string SeriesID = "",
            [ParamDescription("商品层")] string MaterialProperty = "",
            [ParamDescription("页码")] int Page = 1, [ParamDescription("页数")] int Size = 10)
        {
            try
            {
                if (string.IsNullOrEmpty(UserInfo.UserCompanyId))
                {
                    return Error("该用户不属于任何公司!");
                }
                if (string.IsNullOrEmpty(UserInfo.UserExtrSource))
                {
                    return Error("该用户所在公司没有任何数据源!");
                }
                if (Yearv == -1L || Monthv == -1L)
                {
                    var set = new PLPlanYMSetHandler().ReturnPLPlanYMSet();
                    if (set == null) return Error("没有要修改企划的月份");
                    Yearv = set.Yearv.Value;
                    Monthv = set.Monthv.Value;
                }
                var groupBy =
                    @"group by pro.Yearv,pro.Monthv,pro.ParentAreaID,pro.KindID,pro.SeriesID,pro.MaterialProperty";
                var count = ReturnPLModelProcessCount(Yearv, Monthv, ParentAreaID, KindID, SeriesID, MaterialProperty,
                    "", !Convert.ToBoolean(AreaOrShop.Area), Convert.ToInt32(MonthOrWeek.Week), groupBy);
                var result = PaperData<PLModelProcessModel.ListModel>.Create(count, Size, Page);
                var list =
                    ReturnPLModelProcessList(Yearv, Monthv, ParentAreaID, KindID, SeriesID, MaterialProperty, "",
                            !Convert.ToBoolean(AreaOrShop.Area), Convert.ToInt32(MonthOrWeek.Week), Page, Size).GroupBy(
                            d =>
                                    new {d.Yearv, d.Monthv, d.ParentAreaID, d.KindID, d.SeriesID, d.MaterialProperty})
                        .ToList();
                var query = list.Select(item => new PLModelProcessModel.ListModel()
                {
                    Yearv = item.Key.Yearv,
                    Monthv = item.Key.Monthv,
                    ParentAreaID = item.Key.ParentAreaID,
                    ParentAreaName = item.First().ParentAreaName,
                    KindID = item.Key.KindID,
                    KindName = item.First().KindName,
                    SeriesID = item.Key.SeriesID,
                    SeriesName = item.First().SeriesName,
                    MaterialProperty = item.Key.MaterialProperty,
                    list =
                        item.GroupBy(
                            d =>
                                d.Yearv + "_" + d.Monthv + "_" + d.Weekv + "_" + d.ParentAreaID + "_" + d.KindID + "_" +
                                d.SeriesID +
                                "_" + d.MaterialProperty).ToList(),
                }).ToList();
                result.Items = query;
                return Success(result);
            }
            catch (Exception ex)
            {
                return Error(ex.Message);
            }
        }

        /// Writer：梁家健
        /// Create Date：2017-03-21
        [MustLogin]
        [Description("单店-月-品类标准金额占比列表")]
        [ReturnType(typeof(PaperData<IGrouping<string, PLModelProcessModel.ListModel>>))]
        public ApiResult GetPLModelProcessShopMonthList([ParamDescription("企划年")] long Yearv =-1L,
            [ParamDescription("企划月")] long Monthv = -1L, [ParamDescription("商品区域")] string ParentAreaID = "",
            [ParamDescription("类别")] string KindID = "", [ParamDescription("系列")] string SeriesID = "", 
            [ParamDescription("商品层")] string MaterialProperty = "", [ParamDescription("店铺")] string ShopIDs = "",
            [ParamDescription("页码")] int Page = 1, [ParamDescription("页数")] int Size = 10)
        {
            try
            {
                if (string.IsNullOrEmpty(UserInfo.UserCompanyId))
                {
                    return Error("该用户不属于任何公司!");
                }
                if (string.IsNullOrEmpty(UserInfo.UserExtrSource))
                {
                    return Error("该用户所在公司没有任何数据源!");
                }
                if (Yearv == -1L || Monthv == -1L)
                {
                    var set = new PLPlanYMSetHandler().ReturnPLPlanYMSet();
                    if (set == null) return Error("没有要修改企划的月份");
                    Yearv = set.Yearv.Value;
                    Monthv = set.Monthv.Value;
                }
                var shopList = string.IsNullOrEmpty(ShopIDs)
                    ? ""
                    : string.Join(",", ShopIDs.Split(',').Select(item => "'" + item + "'"));
                var groupBy = @"group by pro.Yearv,pro.Monthv,pro.ParentAreaID,pro.ShopID,pro.KindID,pro.SeriesID,pro.MaterialProperty";
                var count = ReturnPLModelProcessCount(Yearv, Monthv, ParentAreaID, KindID, SeriesID, MaterialProperty, shopList, Convert.ToBoolean(AreaOrShop.Area), Convert.ToInt32(MonthOrWeek.Month), groupBy);
                var result = PaperData<IGrouping<string, PLModelProcessModel.ListModel>>.Create(count, Size, Page);
                var list = ReturnPLModelProcessList(Yearv, Monthv, ParentAreaID, KindID, SeriesID, MaterialProperty, shopList, Convert.ToBoolean(AreaOrShop.Area), Convert.ToInt32(MonthOrWeek.Month), Page, Size).GroupBy(
                        d =>
                            d.Yearv + "_" + d.Monthv + "_" + d.ParentAreaID + "_" + d.ShopID + "_" + d.KindID + "_" +
                            d.SeriesID + "_" + d.MaterialProperty)
                            .ToList();
                result.Items = list;
                return Success(result);
            }
            catch (Exception ex)
            {
                return Error(ex.Message);
            }
        }

        /// Writer：梁家健
        /// Create Date：2017-03-21
        [MustLogin]
        [Description("单店-周-品类标准金额占比列表")]
        [ReturnType(typeof(PaperData<PLModelProcessModel.ListModel>))]
        public ApiResult GetPLModelProcessShopWeekList([ParamDescription("企划年")] long Yearv=-1L,
            [ParamDescription("企划月")] long Monthv = -1L, [ParamDescription("商品区域")] string ParentAreaID = "",
            [ParamDescription("类别")] string KindID = "", [ParamDescription("系列")] string SeriesID = "",
            [ParamDescription("商品层")] string MaterialProperty = "", [ParamDescription("店铺")] string ShopIDs = "",
            [ParamDescription("页码")] int Page = 1, [ParamDescription("页数")] int Size = 10)
        {
            try
            {
                if (string.IsNullOrEmpty(UserInfo.UserCompanyId))
                {
                    return Error("该用户不属于任何公司!");
                }
                if (string.IsNullOrEmpty(UserInfo.UserExtrSource))
                {
                    return Error("该用户所在公司没有任何数据源!");
                }
                if (Yearv == -1L || Monthv == -1L)
                {
                    var set = new PLPlanYMSetHandler().ReturnPLPlanYMSet();
                    if (set == null) return Error("没有要修改企划的月份");
                    Yearv = set.Yearv.Value;
                    Monthv = set.Monthv.Value;
                }
                var shopList = string.IsNullOrEmpty(ShopIDs)
                    ? ""
                    : string.Join(",", ShopIDs.Split(',').Select(item => "'" + item + "'"));
                var groupBy = @"group by pro.Yearv,pro.Monthv,pro.ParentAreaID,pro.ShopID,pro.KindID,pro.SeriesID,pro.MaterialProperty";
                var count = ReturnPLModelProcessCount(Yearv, Monthv, ParentAreaID, KindID, SeriesID, MaterialProperty, shopList, Convert.ToBoolean(AreaOrShop.Area), Convert.ToInt32(MonthOrWeek.Week), groupBy);
                var result = PaperData<PLModelProcessModel.ListModel>.Create(count, Size, Page);
                var list = ReturnPLModelProcessList(Yearv, Monthv, ParentAreaID, KindID, SeriesID, MaterialProperty, shopList, Convert.ToBoolean(AreaOrShop.Area), Convert.ToInt32(MonthOrWeek.Week), Page, Size).GroupBy(
                        d =>
                           new { d.Yearv, d.Monthv, d.ParentAreaID, d.ShopID, d.KindID, d.SeriesID, d.MaterialProperty })
                            .ToList();
                var query = list.Select(item => new PLModelProcessModel.ListModel()
                {
                    Yearv = item.Key.Yearv,
                    Monthv = item.Key.Monthv,
                    ParentAreaID = item.Key.ParentAreaID,
                    ParentAreaName = item.First().ParentAreaName,
                    ShopID = item.Key.ShopID,
                    ShopName = item.First().ShopName,
                    KindID = item.Key.KindID,
                    KindName = item.First().KindName,
                    SeriesID = item.Key.SeriesID,
                    SeriesName = item.First().SeriesName,
                    MaterialProperty = item.Key.MaterialProperty,
                    list =
                        item.GroupBy(
                            d =>
                                d.Yearv + "_" + d.Monthv + "_" + d.Weekv + "_" + d.ParentAreaID + "_" + d.ShopID + "_" +
                                d.KindID + "_" +
                                d.SeriesID + "_" + d.MaterialProperty).ToList(),
                }).ToList();
                result.Items = query;
                return Success(result);
            }
            catch (Exception ex)
            {
                return Error(ex.Message);
            }
        }

        /// <summary>
        /// 返回品类数据存储列表
        /// </summary>
        /// <param name="ParentAreaID"></param>
        /// <param name="KindID"></param>
        /// <param name="SeriesID"></param>
        /// <param name="MaterialProperty"></param>
        /// <param name="Keys"></param>
        /// <param name="Page"></param>
        /// <param name="Size"></param>
        /// <param name="isArea"></param>
        /// <param name="isMonth"></param>
        /// <returns></returns>
        /// Writer：梁家健
        /// Create Date：2017-03-21
        public IList<PLModelProcessModel.ListModel> ReturnPLModelProcessList(long Yearv,long Monthv,string ParentAreaID, string KindID, string SeriesID,
            string MaterialProperty, string ShopList, bool isArea, int isMonth,int Page,int Size)
        {
            try
            {
                var con0 = "";
                var con1 = "";
                var con2 = "";
                var con3 = "";
                var con4 = "";
                var con5 = "";
                if (!isArea)
                {
                    con0 = @",shop.ShopCode,shop.ShopName";
                    con1 = $@" INNER JOIN ( SELECT *
                     FROM   ndw.ndw_Shop
                     WHERE  ExtrSource = '{UserInfo.UserExtrSource}'
                            AND CompanyID = '{UserInfo.UserCompanyId}'
                   ) shop ON pro.ExtrSource = shop.ExtrSource
                             AND pro.CompanyID = shop.CompanyID
                             AND pro.ShopID = shop.ShopID ";
                    con2 = $@"{(string.IsNullOrEmpty(ShopList) ? "" : $@" and shop.ShopID in ({ShopList}) ")}";
                    con5 = @" and pro.ShopID=table2.ShopID";
                    if (isMonth == 0)
                    {
                        con3 = @",
                                areaShop.CkPtStandAmountRate*(1-mf.AreaShopFloat)*100 as MinRate,
	                            areaShop.CkPtStandAmountRate*(1+mf.AreaShopFloat)*100 as MaxRate";
                        con4 = $@"
LEFT JOIN ( SELECT  *
                    FROM    ngoods.ngoods_PL_Model_Process
                    WHERE   ExtrSource = '{UserInfo.UserExtrSource}'
                            AND CompanyID = '{UserInfo.UserCompanyId}'
                            AND Yearv = '{Yearv}'
                            AND MonthOrWeek = {Convert.ToInt32(MonthOrWeek.Month)}
                            AND Monthv = '{Monthv}'
                            AND AreaOrShop = {Convert.ToInt32(AreaOrShop.Area)}
                            {(string.IsNullOrEmpty(ParentAreaID) ? "" : $@" and ParentAreaID='{ParentAreaID}' ")}
                            {(string.IsNullOrEmpty(KindID) ? "" : $@" and KindID='{KindID}' ")}
                            {(string.IsNullOrEmpty(SeriesID) ? "" : $@" and SeriesID='{SeriesID}' ")}
                            {(string.IsNullOrEmpty(MaterialProperty) ? "" : $@" and MaterialProperty='{MaterialProperty}' ")}
                  ) areaShop ON table2.ExtrSource = areaShop.ExtrSource
                                AND table2.CompanyID = areaShop.CompanyID
                                AND table2.Yearv = areaShop.Yearv
                                AND table2.Monthv = areaShop.Monthv
                                AND table2.ParentAreaID = areaShop.ParentAreaID
                                AND table2.KindID = areaShop.KindID
                                AND table2.SeriesID = areaShop.SeriesID
                                AND table2.MaterialProperty = areaShop.MaterialProperty
                                AND pro.ModelID = areaShop.ModelID
LEFT JOIN ( SELECT  *
                    FROM    ngoods.ngoods_PL_Bas_ModelFloat
                    WHERE   ExtrSource = '{UserInfo.UserExtrSource}'
                            AND CompanyID = '{UserInfo.UserCompanyId}' 
                  ) mf ON table2.ExtrSource = mf.ExtrSource
                          AND table2.CompanyID = mf.CompanyID
                          AND table2.MaterialProperty = mf.MaterialProperty
                          AND pro.ModelID = mf.ModelID";
                    }
                    else
                    {
                        con3 = @",
                                (CASE WHEN areaShop.CkPtStandAmountRate * ( 1 - mf.AreaShopFloat )
                                           < monthWeek.CkPtStandAmountRate * ( 1  - mf.MonthWeekFloat )
                                     THEN areaShop.CkPtStandAmountRate * ( 1 - mf.AreaShopFloat )
                                     ELSE monthWeek.CkPtStandAmountRate * ( 1 - mf.MonthWeekFloat )
                                END)*100 AS MinRate ,
                                (CASE WHEN areaShop.CkPtStandAmountRate * ( 1 + mf.AreaShopFloat )
                                           > monthWeek.CkPtStandAmountRate * ( 1  + mf.MonthWeekFloat )
                                     THEN areaShop.CkPtStandAmountRate * ( 1 + mf.AreaShopFloat )
                                     ELSE monthWeek.CkPtStandAmountRate * ( 1 + mf.MonthWeekFloat )
                                END)*100 AS MaxRate ";
                        con4 = $@"
        LEFT JOIN ( SELECT  *
                    FROM    ngoods.ngoods_PL_Model_Process
                    WHERE   ExtrSource = '{UserInfo.UserExtrSource}'
                            AND CompanyID = '{UserInfo.UserCompanyId}'
                            AND Yearv = '{Yearv}'
                            AND MonthOrWeek = {Convert.ToInt32(MonthOrWeek.Week)} 
                            AND Monthv = '{Monthv}'
                            AND AreaOrShop = {Convert.ToInt32(AreaOrShop.Area)}
                            {(string.IsNullOrEmpty(ParentAreaID) ? "" : $@" and ParentAreaID='{ParentAreaID}' ")}
                            {(string.IsNullOrEmpty(KindID) ? "" : $@" and KindID='{KindID}' ")}
                            {(string.IsNullOrEmpty(SeriesID) ? "" : $@" and SeriesID='{SeriesID}' ")}
                            {(string.IsNullOrEmpty(MaterialProperty) ? "" : $@" and MaterialProperty='{MaterialProperty}' ")}
                  ) areaShop ON table2.ExtrSource = areaShop.ExtrSource
                                AND table2.CompanyID = areaShop.CompanyID
                                AND table2.Yearv = areaShop.Yearv
                                AND table2.Monthv = areaShop.Monthv
                                AND pro.Weekv = areaShop.Weekv
                                AND areaShop.AreaOrShop = {Convert.ToInt32(AreaOrShop.Area)}
                                AND table2.ParentAreaID = areaShop.ParentAreaID
                                AND table2.KindID = areaShop.KindID
                                AND table2.SeriesID = areaShop.SeriesID
                                AND table2.MaterialProperty = areaShop.MaterialProperty
                                AND pro.ModelID = areaShop.ModelID
        LEFT JOIN ( SELECT  pro.*
                    FROM    ngoods.ngoods_PL_Model_Process pro
                    INNER JOIN ( SELECT *
                                 FROM   ndw.ndw_Shop
                                 WHERE  ExtrSource = '{UserInfo.UserExtrSource}'
                                        AND CompanyID = '{UserInfo.UserCompanyId}'
                               ) shop ON pro.ExtrSource = shop.ExtrSource
                                         AND pro.CompanyID = shop.CompanyID
                                         AND pro.ShopID = shop.ShopID
                    WHERE   pro.ExtrSource = '{UserInfo.UserExtrSource}'
                            AND pro.CompanyID = '{UserInfo.UserCompanyId}'
                            AND pro.Yearv = '{Yearv}'
                            AND pro.MonthOrWeek = {Convert.ToInt32(MonthOrWeek.Month)}
                            AND pro.Monthv = '{Monthv}'
                            AND pro.AreaOrShop = {Convert.ToInt32(AreaOrShop.Shop)}
                            {(string.IsNullOrEmpty(ParentAreaID) ? "" : $@" and pro.ParentAreaID='{ParentAreaID}' ")}
                            {(string.IsNullOrEmpty(KindID) ? "" : $@" and pro.KindID='{KindID}' ")}
                            {(string.IsNullOrEmpty(SeriesID) ? "" : $@" and pro.SeriesID='{SeriesID}' ")}
                            {(string.IsNullOrEmpty(MaterialProperty) ? "" : $@" and pro.MaterialProperty='{MaterialProperty}' ")}
                            {(string.IsNullOrEmpty(ShopList) ? "" : $@" and shop.ShopID in ({ShopList}) ")}
                  ) monthWeek ON table2.ExtrSource = monthWeek.ExtrSource
                                 AND table2.CompanyID = monthWeek.CompanyID
                                 AND table2.Yearv = monthWeek.Yearv
                                 AND table2.Monthv = monthWeek.Monthv
                                 AND table2.ParentAreaID = monthWeek.ParentAreaID
                                 AND table2.ShopID = monthWeek.ShopID
                                 AND table2.KindID = monthWeek.KindID
                                 AND table2.SeriesID = monthWeek.SeriesID
                                 AND table2.MaterialProperty = monthWeek.MaterialProperty
                                 AND pro.ModelID = monthWeek.ModelID
        LEFT JOIN ( SELECT  *
                    FROM    ngoods.ngoods_PL_Bas_ModelFloat
                    WHERE   ExtrSource = '{UserInfo.UserExtrSource}'
                            AND CompanyID = '{UserInfo.UserCompanyId}' 
                  ) mf ON table2.ExtrSource = mf.ExtrSource
                          AND table2.CompanyID = mf.CompanyID
                          AND table2.MaterialProperty = mf.MaterialProperty
                          AND pro.ModelID = mf.ModelID ";
                    }
                }
                else
                {
                    if (isMonth == 1)
                    {
                        con3 = @",
                                monthWeek.CkPtStandAmountRate*(1-mf.MonthWeekFloat)*100 as MinRate,
	                            monthWeek.CkPtStandAmountRate*(1+mf.MonthWeekFloat)*100 as MaxRate";
                        con4 = $@"
        LEFT JOIN ( SELECT  *
                    FROM    ngoods.ngoods_PL_Model_Process
                    WHERE   ExtrSource = '{UserInfo.UserExtrSource}'
                            AND CompanyID = '{UserInfo.UserCompanyId}'
                            AND Yearv = '{Yearv}'
                            AND MonthOrWeek = {Convert.ToInt32(MonthOrWeek.Month)}
                            AND Monthv = '{Monthv}'
                            AND AreaOrShop = {Convert.ToInt32(AreaOrShop.Area)}
                            {(string.IsNullOrEmpty(ParentAreaID) ? "" : $@" and ParentAreaID='{ParentAreaID}' ")}
                            {(string.IsNullOrEmpty(KindID) ? "" : $@" and KindID='{KindID}' ")}
                            {(string.IsNullOrEmpty(SeriesID) ? "" : $@" and SeriesID='{SeriesID}' ")}
                            {(string.IsNullOrEmpty(MaterialProperty) ? "" : $@" and MaterialProperty='{MaterialProperty}' ")}
                  ) monthWeek ON table2.ExtrSource = monthWeek.ExtrSource
                                 AND table2.CompanyID = monthWeek.CompanyID
                                 AND table2.Yearv = monthWeek.Yearv
                                 AND table2.Monthv = monthWeek.Monthv
                                 AND table2.ParentAreaID = monthWeek.ParentAreaID
                                 AND table2.KindID = monthWeek.KindID
                                 AND table2.SeriesID = monthWeek.SeriesID
                                 AND table2.MaterialProperty = monthWeek.MaterialProperty
                                 AND pro.ModelID = monthWeek.ModelID
        LEFT JOIN ( SELECT  *
                    FROM    ngoods.ngoods_PL_Bas_ModelFloat
                    WHERE   ExtrSource = '{UserInfo.UserExtrSource}'
                            AND CompanyID = '{UserInfo.UserCompanyId}' 
                  ) mf ON table2.ExtrSource = mf.ExtrSource
                          AND table2.CompanyID = mf.CompanyID
                          AND table2.MaterialProperty = mf.MaterialProperty
                          AND pro.ModelID = mf.ModelID ";
                    }
                }
                var sql = $@"SELECT  table2.* ,
        pro.Weekv ,
        pro.CompanyID ,
        pro.ModelID ,
        pro.MoveAmountRate * 100 AS MoveAmountRate ,
        pro.SupplyAmountRate * 100 AS SupplyAmountRate ,
        pro.SaleAmountRate * 100 AS SaleAmountRate ,
        pro.MSRate * 100 AS MSRate ,
        pro.StandAmountRate * 100 AS StandAmountRate ,
        pro.CalcStandAmountRate * 100 as CalcStandAmountRate ,
        pro.PtStandAmountRate * 100 AS PtStandAmountRate ,
        pro.AutoStandAmountRate * 100 AS AutoStandAmountRate ,
        ISNULL(pro.CkPtStandAmountRate, pro.PtStandAmountRate) * 100 AS CkPtStandAmountRate ,
        model.ModelName {con3}
FROM    ( SELECT    *
          FROM      ( SELECT    pro.CompanyID ,
                                pro.ExtrSource ,
                                pro.Yearv ,
                                pro.MonthOrWeek ,
                                pro.Monthv ,
                                pro.AreaOrShop ,
                                pro.ParentAreaID ,
                                pro.ShopID ,
                                pro.KindID ,
                                pro.SeriesID ,
                                pro.MaterialProperty ,
                                kind.KindName ,
                                series.SeriesName {con0},
                                dict.dictName AS ParentAreaName ,
                                ROW_NUMBER() OVER ( ORDER BY pro.Yearv DESC, pro.Monthv DESC, pro.ParentAreaID, pro.ShopID, pro.KindID, pro.SeriesID, pro.MaterialProperty ) AS Row
                      FROM      ngoods.ngoods_PL_Model_Process pro
                                INNER JOIN ( SELECT *
                                             FROM   ndw.ndw_global_dictionary
                                             WHERE  ExtrSource = '{UserInfo.UserExtrSource}'
                                                    AND CompanyID = '{UserInfo.UserCompanyId}'
                                           ) dict ON pro.ParentAreaID = dict.dictValue
                                                     AND pro.ExtrSource = dict.ExtrSource
                                                     AND pro.CompanyID = dict.CompanyID
                                INNER JOIN ( SELECT *
                                             FROM   nods.nods_erp_SD_Mat_Kind
                                             WHERE  ExtrSource = '{UserInfo.UserExtrSource}'
                                           ) kind ON pro.KindID = kind.KindID
                                                     AND pro.ExtrSource = kind.ExtrSource
                                INNER JOIN ( SELECT *
                                             FROM   nods.nods_erp_SD_Mat_Series
                                             WHERE  ExtrSource = '{UserInfo.UserExtrSource}'
                                           ) series ON pro.SeriesID = series.SeriesID
                                                       AND pro.ExtrSource = series.ExtrSource
                                {con1}
                      WHERE     pro.ExtrSource = '{UserInfo.UserExtrSource}' AND pro.CompanyID = '{UserInfo.UserCompanyId}' AND pro.Yearv={Yearv} and pro.Monthv={Monthv} and pro.SeriesID !='{TUIPIN}' and pro.MonthOrWeek={isMonth} and pro.AreaOrShop={(isArea ? Convert.ToInt32(AreaOrShop.Area) : Convert.ToInt32(AreaOrShop.Shop))}
                                {(string.IsNullOrEmpty(ParentAreaID) ? "" : $@" and pro.ParentAreaID='{ParentAreaID}' ")}
                                {(string.IsNullOrEmpty(KindID) ? "" : $@" and pro.KindID='{KindID}' ")}
                                {(string.IsNullOrEmpty(SeriesID) ? "" : $@" and pro.SeriesID='{SeriesID}' ")}
                                {(string.IsNullOrEmpty(MaterialProperty) ? "" : $@" and pro.MaterialProperty='{MaterialProperty}' ")}
                                {con2}
                      GROUP BY  pro.CompanyID ,
                                pro.ExtrSource ,
                                pro.Yearv ,
                                pro.MonthOrWeek ,
                                pro.Monthv ,
                                pro.AreaOrShop ,
                                pro.ParentAreaID ,
                                pro.ShopID ,
                                pro.KindID ,
                                pro.SeriesID ,
                                pro.MaterialProperty ,
                                kind.KindName ,
                                series.SeriesName ,
                                dict.dictName {con0}
                    ) table1 where Row BETWEEN ({Page}-1)*{Size}+1 AND {Page}*{Size}
          
        ) table2
        LEFT JOIN ( SELECT  pro.*
                    FROM    ngoods.ngoods_PL_Model_Process pro
					        {con1}
		            WHERE pro.ExtrSource = '{UserInfo.UserExtrSource}' AND pro.CompanyID = '{UserInfo.UserCompanyId}' AND pro.Yearv={Yearv} and pro.Monthv={Monthv} and pro.SeriesID !='{TUIPIN}' and pro.MonthOrWeek={isMonth} and pro.AreaOrShop={(isArea ? Convert.ToInt32(AreaOrShop.Area) : Convert.ToInt32(AreaOrShop.Shop))}
                          {(string.IsNullOrEmpty(ParentAreaID) ? "" : $@" and pro.ParentAreaID='{ParentAreaID}' ")}
                          {(string.IsNullOrEmpty(KindID) ? "" : $@" and pro.KindID='{KindID}' ")}
                          {(string.IsNullOrEmpty(SeriesID) ? "" : $@" and pro.SeriesID='{SeriesID}' ")}
                          {(string.IsNullOrEmpty(MaterialProperty) ? "" : $@" and pro.MaterialProperty='{MaterialProperty}' ")}
                          {con2}
                  ) pro ON pro.ExtrSource = table2.ExtrSource
                           AND pro.CompanyID = table2.CompanyID
                           AND pro.Yearv = table2.Yearv
                           AND pro.MonthOrWeek = table2.MonthOrWeek
                           AND pro.Monthv = table2.Monthv
                           AND pro.AreaOrShop = table2.AreaOrShop
                           AND pro.ParentAreaID = table2.ParentAreaID
                           AND pro.KindID = table2.KindID
                           AND pro.SeriesID = table2.SeriesID
                           AND pro.MaterialProperty = table2.MaterialProperty {con5}
        INNER JOIN ( SELECT *
                     FROM   nods.nods_erp_SD_Mat_Model 
                     WHERE  ExtrSource = '{UserInfo.UserExtrSource}'
                   ) model ON pro.ModelID = model.ModelID
                        AND table2.ExtrSource = model.ExtrSource
        {con4}
ORDER BY table2.Yearv DESC ,
        table2.Monthv DESC ,
        table2.ParentAreaID ,
        table2.ShopID ,
        table2.KindID ,
        table2.SeriesID ,
        table2.MaterialProperty ,
        pro.ModelID ,
        pro.Weekv";
                var cmd = Helper.GetSqlStringCommand(sql);
                var dt = Helper.ExecuteDataTable(cmd);
                return ModelConvertHelper<PLModelProcessModel.ListModel>.ConvertToModel(dt);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }
        
        /// <summary>
        /// 返回品类数据存储总条数
        /// </summary>
        /// <param name="ParentAreaID"></param>
        /// <param name="KindID"></param>
        /// <param name="SeriesID"></param>
        /// <param name="MaterialProperty"></param>
        /// <param name="Keys"></param>
        /// <param name="isArea"></param>
        /// <param name="isMonth"></param>
        /// <returns></returns>
        /// Writer：梁家健
        /// Create Date：2017-03-21
        public int ReturnPLModelProcessCount(long Yearv, long Monthv, string ParentAreaID, string KindID, string SeriesID, string MaterialProperty, string ShopList, bool isArea, int isMonth, string groupBy)
        {
            try
            {
                var con1 = "";
                var con2 = "";
                if (!isArea)
                {
                    con1 = $@" INNER JOIN ( SELECT *
                     FROM   ndw.ndw_Shop
                     WHERE  ExtrSource = '{UserInfo.UserExtrSource}'
                            AND CompanyID = '{UserInfo.UserCompanyId}'
                   ) shop ON pro.ExtrSource = shop.ExtrSource
                             AND pro.CompanyID = shop.CompanyID
                             AND pro.ShopID = shop.ShopID ";
                    con2 = $@" {(string.IsNullOrEmpty(ShopList) ? "" : $@" and shop.ShopID in ({ShopList}) ")} ";
                }
                var sql = $@"SELECT  COUNT(1)
FROM    ( SELECT    COUNT(1)
          FROM      ngoods.ngoods_PL_Model_Process pro
                    INNER JOIN ( SELECT *
                                 FROM   ndw.ndw_global_dictionary
                                 WHERE  ExtrSource = '{UserInfo.UserExtrSource}'
                                        AND CompanyID = '{UserInfo.UserCompanyId}'
                               ) dict ON pro.ParentAreaID = dict.dictValue
                                         AND pro.ExtrSource = dict.ExtrSource
                                         AND pro.CompanyID = dict.CompanyID
                    INNER JOIN ( SELECT *
                                 FROM   nods.nods_erp_SD_Mat_Kind
                                 WHERE  ExtrSource = '{UserInfo.UserExtrSource}'
                               ) kind ON pro.KindID = kind.KindID
                                         AND pro.ExtrSource = kind.ExtrSource
                    INNER JOIN ( SELECT *
                                 FROM   nods.nods_erp_SD_Mat_Series
                                 WHERE  ExtrSource = '{UserInfo.UserExtrSource}'
                               ) series ON pro.SeriesID = series.SeriesID
                                           AND pro.ExtrSource = series.ExtrSource
                    INNER JOIN ( SELECT *
                                 FROM   nods.nods_erp_SD_Mat_Model
                                 WHERE  ExtrSource = '{UserInfo.UserExtrSource}'
                               ) model ON pro.ModelID = model.ModelID
                                          AND pro.ExtrSource = model.ExtrSource
                    {con1}
          WHERE     pro.ExtrSource = '{UserInfo.UserExtrSource}' 
                    AND pro.CompanyID = '{UserInfo.UserCompanyId}'
                    AND pro.Yearv = '{Yearv}'
                    AND pro.Monthv = '{Monthv}'
                    AND pro.MonthOrWeek = {isMonth}
                    AND pro.AreaOrShop = {(isArea ? 0 : 1)}
                    AND pro.SeriesID != '{TUIPIN}'
                    {(string.IsNullOrEmpty(ParentAreaID) ? "" : $@" and pro.ParentAreaID='{ParentAreaID}' ")}
                    {(string.IsNullOrEmpty(KindID) ? "" : $@" and pro.KindID='{KindID}' ")}
                    {(string.IsNullOrEmpty(SeriesID) ? "" : $@" and pro.SeriesID='{SeriesID}' ")}
                    {(string.IsNullOrEmpty(MaterialProperty) ? "" : $@" and pro.MaterialProperty='{MaterialProperty}' ")}
                    {con2}
          {groupBy}
        ) table1";
                var cmd = Helper.GetSqlStringCommand(sql);
                return Convert.ToInt32(Helper.ExecuteScalar(cmd));
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        /// <summary>
        /// 返回品类参数浮动值
        /// </summary>
        /// <returns></returns>
        /// Writer：梁家健
        /// Create Date：2017-03-21
        public IList<PLBasModelFloatModel.NeceModel> ReturnPLBasModelFloatList()
        {
            try
            {
                var sql = $@"SELECT  *
FROM    Ngoods.Ngoods_PL_Bas_ModelFloat 
WHERE   ExtrSource = '{UserInfo.UserExtrSource}'
        AND CompanyID = '{UserInfo.UserCompanyId}'";
                var cmd = Helper.GetSqlStringCommand(sql);
                var dt = Helper.ExecuteDataTable(cmd);
                return ModelConvertHelper<PLBasModelFloatModel.NeceModel>.ConvertToModel(dt);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        /// Writer：梁家健
        /// Create Date：2017-03-21
        [JsonType]
        [MustLogin]
        [RoleBc]
        [Description("修改品类数据存储")]
        [ReturnType(typeof(int),"0-成功")]
        public ApiResult UpdateAreaPLModelProcess(PLModelProcessModel.ModifyModel param)
        {
            try
            {
                const string sql = @"UPDATE ngoods.ngoods_PL_Model_Process
SET CkPtStandAmountRate={{0}},CheckState=1
where Yearv={{1}} and MonthOrWeek={{2}} and Monthv={{3}} and AreaOrShop={{4}} and ParentAreaID='{{5}}' and KindID='{{6}}' and SeriesID='{{7}}' and MaterialProperty='{{8}}' and ModelID='{{9}}'
{0} {1} {2}";
                if (param == null) return Error("参数对象为空");
                if (string.IsNullOrEmpty(UserInfo.UserCompanyId))
                {
                    return Error("该用户不属于任何公司!");
                }
                if (string.IsNullOrEmpty(UserInfo.UserExtrSource))
                {
                    return Error("该用户所在公司没有任何数据源!");
                }
                var con = $@" AND ExtrSource='{UserInfo.UserExtrSource}' AND CompanyID='{UserInfo.UserCompanyId}' ";
                if (param.list != null)
                {
                    var cmdList = new List<CommandInfo>();
                    foreach (var item in param.list)
                    {
                        var cmd = new CommandInfo()
                        {
                            CommandText =
                                string.Format(
                                    string.Format(sql, param.isMonth == 0 ? "" : $@" and Weekv={item.Weekv} ",
                                        param.isArea ? "" : $@" and ShopID='{item.ShopID}' ", con),
                                    item.CkPtStandAmountRate/(decimal) 100.0, item.Yearv, item.MonthOrWeek,
                                    item.Monthv, item.AreaOrShop, item.ParentAreaID, item.KindID, item.SeriesID,
                                    item.MaterialProperty, item.ModelID),
                        };
                        cmdList.Add(cmd);
                    }
                    Helper.ExecuteSqlTran(cmdList);
                }
                return Success(0);
            }
            catch (Exception ex)
            {
                return Error(ex.Message);
            }
        }

        /// Writer：梁家健
        /// Create Date：2017-04-27
        [JsonType]
        [MustLogin]
        [Description("修改品类数据存储")]
        [ReturnType(typeof(int), "0-成功")]
        public ApiResult UpdateShopPLModelProcess(PLModelProcessModel.ModifyModel param)
        {
            try
            {
                const string sql = @"UPDATE  ngoods.ngoods_PL_Model_Process
SET     CkPtStandAmountRate = {{0}} ,
        CheckState = 1
WHERE   Yearv = {{1}}
        AND MonthOrWeek = {{2}}
        AND Monthv = {{3}}
        AND AreaOrShop = {{4}}
        AND ParentAreaID = '{{5}}'
        AND KindID = '{{6}}'
        AND SeriesID = '{{7}}'
        AND MaterialProperty = '{{8}}'
        AND ModelID = '{{9}}'
{0} {1} {2}";
                if (param == null) return Error("参数对象为空");
                if (string.IsNullOrEmpty(UserInfo.UserCompanyId))
                {
                    return Error("该用户不属于任何公司!");
                }
                if (string.IsNullOrEmpty(UserInfo.UserExtrSource))
                {
                    return Error("该用户所在公司没有任何数据源!");
                }
                var con = $@" AND ExtrSource='{UserInfo.UserExtrSource}' AND CompanyID='{UserInfo.UserCompanyId}' ";
                if (param.list != null)
                {
                    var cmdList = new List<CommandInfo>();
                    foreach (var item in param.list)
                    {
                        var cmd = new CommandInfo()
                        {
                            CommandText =
                                string.Format(
                                    string.Format(sql, param.isMonth == 0 ? "" : $@" and Weekv={item.Weekv} ",
                                        param.isArea ? "" : $@" and ShopID='{item.ShopID}' ", con),
                                    item.CkPtStandAmountRate/(decimal) 100.0, item.Yearv, item.MonthOrWeek,
                                    item.Monthv, item.AreaOrShop, item.ParentAreaID, item.KindID, item.SeriesID,
                                    item.MaterialProperty, item.ModelID),
                        };
                        cmdList.Add(cmd);
                    }
                    Helper.ExecuteSqlTran(cmdList);
                }
                return Success(0);
            }
            catch (Exception ex)
            {
                return Error(ex.Message);
            }
        }

        /// Writer：梁家健
        /// Create Date：2017-05-22
        [JsonType]
        [MustLogin]
        [RoleBc]
        [Description("修改品类数据存储（区域-月）")]
        [ReturnType(typeof(int), "0-成功")]
        public ApiResult UpdateMonthAreaPLModelProcess(PLModelProcessModel.ModifyModel param)
        {
            try
            {
                if (string.IsNullOrEmpty(UserInfo.UserCompanyId))
                {
                    return Error("该用户不属于任何公司!");
                }
                if (string.IsNullOrEmpty(UserInfo.UserExtrSource))
                {
                    return Error("该用户所在公司没有任何数据源!");
                }
                var sql = $@"UPDATE  ngoods.ngoods_PL_Model_Process
SET     CkPtStandAmountRate = {{0}} ,
        CheckState = 1
WHERE   Yearv = {{1}}
        AND MonthOrWeek = {{2}}
        AND Monthv = {{3}}
        AND AreaOrShop = {{4}}
        AND ParentAreaID = '{{5}}'
        AND KindID = '{{6}}'
        AND SeriesID = '{{7}}'
        AND MaterialProperty = '{{8}}'
        AND ModelID = '{{9}}'
        AND ExtrSource = '{UserInfo.UserExtrSource}'
        AND CompanyID = '{UserInfo.UserCompanyId}' ";
                if (param == null) return Error("参数对象为空");
                if (param.list != null)
                {
                    var cmdList = new List<CommandInfo>();
                    foreach (var item in param.list)
                    {
                        var cmd = new CommandInfo()
                        {
                            CommandText =
                                string.Format(
                                    sql, item.CkPtStandAmountRate / (decimal)100.0, item.Yearv,
                                    item.MonthOrWeek,
                                    item.Monthv, item.AreaOrShop, item.ParentAreaID, item.KindID, item.SeriesID,
                                    item.MaterialProperty, item.ModelID),
                        };
                        cmdList.Add(cmd);
                    }
                    Helper.ExecuteSqlTran(cmdList);
                    ModifyWeekAreaPLModelProcess(param.list.First());
                    ModifyMonthShopPLModelProcess(param.list.First());
                    ModifyWeekShopPLModelProcess(param.list.First());
                }
                return Success(0);
            }
            catch (Exception ex)
            {
                return Error(ex.Message);
            }
        }

        /// Writer：梁家健
        /// Create Date：2017-05-22
        [JsonType]
        [MustLogin]
        [RoleBc]
        [Description("修改品类数据存储（区域-周）")]
        [ReturnType(typeof(int), "0-成功")]
        public ApiResult UpdateWeekAreaPLModelProcess(PLModelProcessModel.ModifyModel param)
        {
            try
            {
                if (string.IsNullOrEmpty(UserInfo.UserCompanyId))
                {
                    return Error("该用户不属于任何公司!");
                }
                if (string.IsNullOrEmpty(UserInfo.UserExtrSource))
                {
                    return Error("该用户所在公司没有任何数据源!");
                }
                var sql = $@"UPDATE  ngoods.ngoods_PL_Model_Process
SET     CkPtStandAmountRate = {{0}} ,
        CheckState = 1
WHERE   Yearv = {{1}}
        AND MonthOrWeek = {{2}}
        AND Monthv = {{3}}
        AND AreaOrShop = {{4}}
        AND ParentAreaID = '{{5}}'
        AND KindID = '{{6}}'
        AND SeriesID = '{{7}}'
        AND MaterialProperty = '{{8}}'
        AND ModelID = '{{9}}'
        AND Weekv = '{{10}}'
        AND ExtrSource = '{UserInfo.UserExtrSource}'
        AND CompanyID = '{UserInfo.UserCompanyId}' ";
                if (param == null) return Error("参数对象为空");
                if (param.list != null)
                {
                    var cmdList = new List<CommandInfo>();
                    foreach (var item in param.list)
                    {
                        var cmd = new CommandInfo()
                        {
                            CommandText =
                                string.Format(
                                    sql, item.CkPtStandAmountRate / (decimal)100.0, item.Yearv,
                                    item.MonthOrWeek,
                                    item.Monthv, item.AreaOrShop, item.ParentAreaID, item.KindID, item.SeriesID,
                                    item.MaterialProperty, item.ModelID,item.Weekv),
                        };
                        cmdList.Add(cmd);
                    }
                    Helper.ExecuteSqlTran(cmdList);
                    ModifyWeekShopPLModelProcess(param.list.First());
                }
                return Success(0);
            }
            catch (Exception ex)
            {
                return Error(ex.Message);
            }
        }

        /// Writer：梁家健
        /// Create Date：2017-05-22
        [JsonType]
        [MustLogin]
        [RoleBc]
        [Description("修改品类数据存储（单店-月）")]
        [ReturnType(typeof(int), "0-成功")]
        public ApiResult UpdateMonthShopPLModelProcess(PLModelProcessModel.ModifyModel param)
        {
            try
            {
                if (string.IsNullOrEmpty(UserInfo.UserCompanyId))
                {
                    return Error("该用户不属于任何公司!");
                }
                if (string.IsNullOrEmpty(UserInfo.UserExtrSource))
                {
                    return Error("该用户所在公司没有任何数据源!");
                }
                var sql = $@"UPDATE  ngoods.ngoods_PL_Model_Process
SET     CkPtStandAmountRate = {{0}} ,
        CheckState = 1
WHERE   Yearv = {{1}}
        AND MonthOrWeek = {{2}}
        AND Monthv = {{3}}
        AND AreaOrShop = {{4}}
        AND ParentAreaID = '{{5}}'
        AND KindID = '{{6}}'
        AND SeriesID = '{{7}}'
        AND MaterialProperty = '{{8}}'
        AND ModelID = '{{9}}'
        AND ShopID = '{{10}}'
        AND ExtrSource = '{UserInfo.UserExtrSource}'
        AND CompanyID = '{UserInfo.UserCompanyId}' ";
                if (param == null) return Error("参数对象为空");
                if (param.list != null)
                {
                    var cmdList = new List<CommandInfo>();
                    foreach (var item in param.list)
                    {
                        var cmd = new CommandInfo()
                        {
                            CommandText =
                                string.Format(
                                    sql, item.CkPtStandAmountRate / (decimal)100.0, item.Yearv,
                                    item.MonthOrWeek,
                                    item.Monthv, item.AreaOrShop, item.ParentAreaID, item.KindID, item.SeriesID,
                                    item.MaterialProperty, item.ModelID,item.ShopID),
                        };
                        cmdList.Add(cmd);
                    }
                    Helper.ExecuteSqlTran(cmdList);
                    ModifyWeekShopPLModelProcess(param.list.First());
                }
                return Success(0);
            }
            catch (Exception ex)
            {
                return Error(ex.Message);
            }
        }

        /// Writer：梁家健
        /// Create Date：2017-05-22
        [JsonType]
        [MustLogin]
        [RoleBc]
        [Description("修改品类数据存储（单店-周）")]
        [ReturnType(typeof(int), "0-成功")]
        public ApiResult UpdateWeekShopPLModelProcess(PLModelProcessModel.ModifyModel param)
        {
            try
            {
                if (string.IsNullOrEmpty(UserInfo.UserCompanyId))
                {
                    return Error("该用户不属于任何公司!");
                }
                if (string.IsNullOrEmpty(UserInfo.UserExtrSource))
                {
                    return Error("该用户所在公司没有任何数据源!");
                }
                var sql = $@"UPDATE  ngoods.ngoods_PL_Model_Process
SET     CkPtStandAmountRate = {{0}} ,
        CheckState = 1
WHERE   Yearv = {{1}}
        AND MonthOrWeek = {{2}}
        AND Monthv = {{3}}
        AND AreaOrShop = {{4}}
        AND ParentAreaID = '{{5}}'
        AND KindID = '{{6}}'
        AND SeriesID = '{{7}}'
        AND MaterialProperty = '{{8}}'
        AND ModelID = '{{9}}'
        AND Weekv = '{{10}}'
        AND ShopID = '{{11}}'
        AND ExtrSource = '{UserInfo.UserExtrSource}'
        AND CompanyID = '{UserInfo.UserCompanyId}' ";
                if (param == null) return Error("参数对象为空");
                if (param.list != null)
                {
                    var cmdList = new List<CommandInfo>();
                    foreach (var item in param.list)
                    {
                        var cmd = new CommandInfo()
                        {
                            CommandText =
                                string.Format(
                                    sql, item.CkPtStandAmountRate / (decimal)100.0, item.Yearv,
                                    item.MonthOrWeek,
                                    item.Monthv, item.AreaOrShop, item.ParentAreaID, item.KindID, item.SeriesID,
                                    item.MaterialProperty, item.ModelID, item.Weekv,item.ShopID),
                        };
                        cmdList.Add(cmd);
                    }
                    Helper.ExecuteSqlTran(cmdList);
                }
                return Success(0);
            }
            catch (Exception ex)
            {
                return Error(ex.Message);
            }
        }

        /// <summary>
        /// 区域-周
        /// </summary>
        /// <param name="param"></param>
        /// Writer：梁家健
        /// Create Date：2017-05-22
        public void ModifyWeekAreaPLModelProcess(PLModelProcessModel.NeceModel param)
        {
            try
            {
                var sqlPtStandAmountRate = $@"UPDATE  ngoods.ngoods_PL_Model_Process a  
set     PtStandAmountRate = CASE WHEN a.CalcStandAmountRate > PtStandAmountRate_p
                                  THEN PtStandAmountRate_p
                                  WHEN a.CalcStandAmountRate < PtStandAmountRate_m
                                  THEN PtStandAmountRate_m
                                  ELSE a.CalcStandAmountRate
                             END
FROM    ( SELECT DISTINCT
                        a.ExtrSource ,
                        a.CompanyID ,
                        a.Yearv ,
                        a.Monthv ,
                        a.ParentAreaID ,
                        a.KindID ,
                        a.SeriesID ,
                        a.MaterialProperty ,
                        a.ModelID ,
                        CkPtStandAmountRate * ( 1 + ISNULL(b.MonthWeekFloat,
                                                           0.1) ) AS PtStandAmountRate_p ,
                        CkPtStandAmountRate * ( 1 - ISNULL(b.MonthWeekFloat,
                                                           0.1) ) AS PtStandAmountRate_m
               FROM     ngoods.ngoods_PL_Model_Process a
                        LEFT JOIN ( SELECT  *
                                    FROM    ngoods.ngoods_PL_Bas_ModelFloat
                                    WHERE   ExtrSource = '{UserInfo.UserExtrSource}'
                                            AND CompanyID = '{UserInfo.UserCompanyId}'
                                  ) b ON a.ExtrSource = b.ExtrSource
                                         AND a.CompanyID = b.CompanyID
                                         AND a.MaterialProperty = b.MaterialProperty
                                         AND a.ModelID = b.ModelID
               WHERE    ISNULL(a.MonthOrWeek, 0) = 0
                        AND ISNULL(a.AreaOrShop, 0) = 0
                        AND a.ExtrSource = '{UserInfo.UserExtrSource}'
                        AND a.CompanyID = '{UserInfo.UserCompanyId}'
                        AND a.Yearv = '{param.Yearv.Value}'
                        AND a.Monthv = '{param.Monthv.Value}'
                        AND a.ParentAreaID = '{param.ParentAreaID}'
                        AND a.KindID = '{param.KindID}'
                        AND a.SeriesID = '{param.SeriesID}'
                        AND a.MaterialProperty = '{param.MaterialProperty}'
             ) b WHERE b.ExtrSource = a.ExtrSource
                    AND b.CompanyID = a.CompanyID
                    AND b.Yearv = a.Yearv
                    AND b.Monthv = a.Monthv
                    AND b.ParentAreaID = a.ParentAreaID
                    AND b.KindID = a.KindID
                    AND b.SeriesID = a.SeriesID
                    AND b.MaterialProperty = a.MaterialProperty
                    AND b.ModelID = a.ModelID
                    AND ISNULL(MonthOrWeek, 0) = 1
                    AND ISNULL(AreaOrShop, 0) = 0";
                var sqlAutoStandAmountRate = $@"UPDATE  ngoods.ngoods_PL_Model_Process a
SET     AutoStandAmountRate = CASE WHEN m.PtStandAmountRate = 0 THEN 0
                                   ELSE a.PtStandAmountRate
                                        / m.PtStandAmountRate
                              END ,
        CkPtStandAmountRate = CASE WHEN m.PtStandAmountRate = 0 THEN 0
                                   ELSE a.PtStandAmountRate
                                        / m.PtStandAmountRate
                              END
FROM    ( SELECT   ExtrSource ,
                        CompanyID ,
                        Yearv ,
                        Weekv ,
                        ParentAreaID ,
                        KindID ,
                        SeriesID ,
                        MaterialProperty ,
                        SUM(PtStandAmountRate) AS PtStandAmountRate
               FROM     ngoods.ngoods_PL_Model_Process
               WHERE    ISNULL(MonthOrWeek, 0) = 1
                        AND ISNULL(AreaOrShop, 0) = 0
                        AND ExtrSource = '{UserInfo.UserExtrSource}'
                        AND CompanyID = '{UserInfo.UserCompanyId}'
                        AND Yearv = '{param.Yearv.Value}'
                        AND Monthv = '{param.Monthv.Value}'
                        AND ParentAreaID = '{param.ParentAreaID}'
                        AND KindID = '{param.KindID}'
                        AND SeriesID = '{param.SeriesID}'
                        AND MaterialProperty = '{param.MaterialProperty}'
               GROUP BY ExtrSource ,
                        CompanyID ,
                        Yearv ,
                        Weekv ,
                        ParentAreaID ,
                        KindID ,
                        SeriesID ,
                        MaterialProperty
             ) m WHERE m.ExtrSource = a.ExtrSource
                    AND m.CompanyID = a.CompanyID
                    AND m.Yearv = a.Yearv
                    AND m.Weekv = a.Weekv
                    AND m.KindID = a.KindID
                    AND m.ParentAreaID = a.ParentAreaID
                    AND m.SeriesID = a.SeriesID
                    AND m.MaterialProperty = a.MaterialProperty
                    AND ISNULL(MonthOrWeek, 0) = 1
                    AND ISNULL(AreaOrShop, 0) = 0";
                var sql = $@"UPDATE  ngoods.ngoods_PL_Model_Process a
SET     AutoStandAmountRate = a.AutoStandAmountRate - b.DiffPt ,
        CkPtStandAmountRate = a.AutoStandAmountRate - b.DiffPt
FROM    ( SELECT   a.ExtrSource ,
                        a.CompanyID ,
                        a.Yearv ,
                        a.Weekv ,
                        a.ParentAreaID ,
                        a.KindID ,
                        a.SeriesID ,
                        a.MaterialProperty ,
                        ModelID ,
                        b.AutoStandAmountRate - 1 AS DiffPt ,
                        ROW_NUMBER() OVER ( PARTITION BY a.Yearv, a.Weekv,
                                            a.ParentAreaID, a.KindID,
                                            a.SeriesID, a.MaterialProperty ORDER BY StandAmountRate DESC ) AS RowNum
               FROM     ngoods.ngoods_PL_Model_Process a
                        INNER JOIN ( SELECT ExtrSource ,
                                            CompanyID ,
                                            Yearv ,
                                            Weekv ,
                                            ParentAreaID ,
                                            KindID ,
                                            SeriesID ,
                                            MaterialProperty ,
                                            SUM(AutoStandAmountRate) AS AutoStandAmountRate
                                     FROM   ngoods.ngoods_PL_Model_Process
                                     WHERE  ISNULL(MonthOrWeek, 0) = 1
                                            AND ISNULL(AreaOrShop, 0) = 0
                                     GROUP BY ExtrSource ,
                                            CompanyID ,
                                            Yearv ,
                                            Weekv ,
                                            ParentAreaID ,
                                            KindID ,
                                            SeriesID ,
                                            MaterialProperty
                                     HAVING SUM(AutoStandAmountRate) <> 1
                                            AND SUM(AutoStandAmountRate) > 0
                                   ) b ON a.ExtrSource = b.ExtrSource
                                          AND a.CompanyID = b.CompanyID
                                          AND a.Yearv = b.Yearv
                                          AND a.Weekv = b.Weekv
                                          AND b.ParentAreaID = a.ParentAreaID
                                          AND a.KindID = b.KindID
                                          AND a.SeriesID = b.SeriesID
                                          AND a.MaterialProperty = b.MaterialProperty
               WHERE    a.ExtrSource = '{UserInfo.UserExtrSource}'
                        AND a.CompanyID = '{UserInfo.UserCompanyId}'
                        AND a.Yearv = '{param.Yearv.Value}'
                        AND a.Monthv = '{param.Monthv.Value}'
                        AND a.ParentAreaID = '{param.ParentAreaID}'
                        AND a.KindID = '{param.KindID}'
                        AND a.SeriesID = '{param.SeriesID}'
                        AND a.MaterialProperty = '{param.MaterialProperty}'
             ) b WHERE b.RowNum = 1
                    AND a.ExtrSource = b.ExtrSource
                    AND a.CompanyID = b.CompanyID
                    AND a.ParentAreaID = b.ParentAreaID
                    AND a.Yearv = b.Yearv
                    AND a.Weekv = b.Weekv
                    AND a.KindID = b.KindID
                    AND a.SeriesID = b.SeriesID
                    AND a.MaterialProperty = b.MaterialProperty
                    AND a.ModelID = b.ModelID
                    AND ISNULL(MonthOrWeek, 0) = 1
                    AND ISNULL(AreaOrShop, 0) = 0";
                var cmdList = new List<CommandInfo>()
                {
                    new CommandInfo
                    {
                        CommandText = sqlPtStandAmountRate,
                    },
                    new CommandInfo
                    {
                        CommandText = sqlAutoStandAmountRate,
                    },
                    new CommandInfo
                    {
                        CommandText = sql,
                    }
                };
                Helper.ExecuteSqlTran(cmdList);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        /// <summary>
        /// 单店-月
        /// </summary>
        /// <param name="param"></param>
        /// Writer：梁家健
        /// Create Date：2017-05-22
        public void ModifyMonthShopPLModelProcess(PLModelProcessModel.NeceModel param)
        {
            try
            {
                var sqlPtStandAmountRate = $@"UPDATE  ngoods.ngoods_PL_Model_Process a
SET     PtStandAmountRate = CASE WHEN a.CalcStandAmountRate > PtStandAmountRate_p
                                 THEN PtStandAmountRate_p
                                 WHEN a.CalcStandAmountRate < PtStandAmountRate_m
                                 THEN PtStandAmountRate_m
                                 ELSE a.CalcStandAmountRate
                            END
FROM    ( SELECT DISTINCT
                        a.ExtrSource ,
                        a.CompanyID ,
                        a.Yearv ,
                        a.Monthv ,
                        a.ParentAreaID ,
                        a.KindID ,
                        a.SeriesID ,
                        a.MaterialProperty ,
                        a.ModelID ,
                        CkPtStandAmountRate * ( 1 + ISNULL(b.AreaShopFloat,
                                                           0.2) ) AS PtStandAmountRate_p ,
                        CkPtStandAmountRate * ( 1 - ISNULL(b.AreaShopFloat,
                                                           0.2) ) AS PtStandAmountRate_m
               FROM     ngoods.ngoods_PL_Model_Process a
                        LEFT JOIN ( SELECT  *
                                    FROM    ngoods.ngoods_PL_Bas_ModelFloat
                                    WHERE   ExtrSource = '{UserInfo.UserExtrSource}'
                                            AND CompanyID = '{UserInfo.UserCompanyId}'
                                  ) b ON a.ExtrSource = b.ExtrSource
                                         AND a.CompanyID = b.CompanyID
                                         AND a.MaterialProperty = b.MaterialProperty
                                         AND a.ModelID = b.ModelID
               WHERE    ISNULL(a.MonthOrWeek, 0) = 0
                        AND ISNULL(a.AreaOrShop, 0) = 0
                        AND a.ExtrSource = '{UserInfo.UserExtrSource}'
                        AND a.CompanyID = '{param.CompanyID}'
                        AND a.Yearv = '{param.Yearv.Value}'
                        AND a.Monthv = '{param.Monthv.Value}'
                        AND a.ParentAreaID = '{param.ParentAreaID}'
                        AND a.KindID = '{param.KindID}'
                        AND a.SeriesID = '{param.SeriesID}'
                        AND a.MaterialProperty = '{param.MaterialProperty}'
             ) b WHERE b.ExtrSource = a.ExtrSource
                    AND b.CompanyID = a.CompanyID
                    AND b.Yearv = a.Yearv
                    AND b.Monthv = a.Monthv
                    AND b.ParentAreaID = a.ParentAreaID
                    AND b.KindID = a.KindID
                    AND b.SeriesID = a.SeriesID
                    AND b.MaterialProperty = a.MaterialProperty
                    AND b.ModelID = a.ModelID
                    AND ISNULL(MonthOrWeek, 0) = 0
                    AND ISNULL(AreaOrShop, 0) = 1";
                var sqlAutoStandAmountRate = $@"UPDATE  ngoods.ngoods_PL_Model_Process a
SET     AutoStandAmountRate = CASE WHEN m.PtStandAmountRate = 0 THEN 0
                                   ELSE a.PtStandAmountRate
                                        / m.PtStandAmountRate
                              END ,
        CkPtStandAmountRate = CASE WHEN m.PtStandAmountRate = 0 THEN 0
                                   ELSE a.PtStandAmountRate
                                        / m.PtStandAmountRate
                              END
FROM    ( SELECT ExtrSource ,
        CompanyID ,
        Yearv ,
        Monthv ,
        ParentAreaID ,
        ShopID ,
        KindID ,
        SeriesID ,
        MaterialProperty ,
        SUM(PtStandAmountRate) AS PtStandAmountRate
 FROM   ngoods.ngoods_PL_Model_Process
 WHERE  ISNULL(MonthOrWeek, 0) = 0
        AND ISNULL(AreaOrShop, 0) = 1
        AND ExtrSource = '{UserInfo.UserExtrSource}'
        AND CompanyID = '{param.CompanyID}'
        AND Yearv = '{param.Yearv.Value}'
        AND Monthv = '{param.Monthv.Value}'
        AND ParentAreaID = '{param.ParentAreaID}'
        AND KindID = '{param.KindID}'
        AND SeriesID = '{param.SeriesID}'
        AND MaterialProperty = '{param.MaterialProperty}'
 GROUP BY ExtrSource ,
        CompanyID ,
        Yearv ,
        Monthv ,
        ParentAreaID ,
        ShopID ,
        KindID ,
        SeriesID ,
        MaterialProperty
        ) m WHERE m.ExtrSource = a.ExtrSource
               AND m.CompanyID = a.CompanyID
               AND m.Yearv = a.Yearv
               AND m.Monthv = a.Monthv
               AND m.KindID = a.KindID
               AND m.ParentAreaID = a.ParentAreaID
               AND m.ShopID = a.ShopID
               AND m.SeriesID = a.SeriesID
               AND m.MaterialProperty = a.MaterialProperty
               AND ISNULL(MonthOrWeek, 0) = 0
               AND ISNULL(AreaOrShop, 0) = 1";
                var sql = $@"UPDATE  ngoods.ngoods_PL_Model_Process a
SET     AutoStandAmountRate = a.AutoStandAmountRate - b.DiffPt ,
        CkPtStandAmountRate = a.AutoStandAmountRate - b.DiffPt
FROM    ( SELECT   a.ExtrSource ,
                        a.CompanyID ,
                        a.Yearv ,
                        a.Monthv ,
                        a.ShopID ,
                        a.KindID ,
                        a.SeriesID ,
                        a.MaterialProperty ,
                        ModelID ,
                        a.AutoStandAmountRate ,
                        b.AutoStandAmountRate - 1 AS DiffPt ,
                        ROW_NUMBER() OVER ( PARTITION BY a.Yearv, a.Monthv,
                                            a.ShopID, a.KindID, a.SeriesID,
                                            a.MaterialProperty ORDER BY a.AutoStandAmountRate DESC ) AS RowNum
               FROM     ngoods.ngoods_PL_Model_Process a
                        INNER JOIN ( SELECT ExtrSource ,
                                            CompanyID ,
                                            Yearv ,
                                            Monthv ,
                                            ShopID ,
                                            KindID ,
                                            SeriesID ,
                                            MaterialProperty ,
                                            SUM(AutoStandAmountRate) AS AutoStandAmountRate
                                     FROM   ngoods.ngoods_PL_Model_Process
                                     WHERE  ISNULL(MonthOrWeek, 0) = 0
                                            AND ISNULL(AreaOrShop, 0) = 1
                                     GROUP BY ExtrSource ,
                                            CompanyID ,
                                            Yearv ,
                                            Monthv ,
                                            ShopID ,
                                            KindID ,
                                            SeriesID ,
                                            MaterialProperty
                                     HAVING SUM(AutoStandAmountRate) <> 1
                                            AND SUM(AutoStandAmountRate) > 0
                                   ) b ON a.ExtrSource = b.ExtrSource
                                          AND a.CompanyID = b.CompanyID
                                          AND a.Yearv = b.Yearv
                                          AND b.Monthv = a.Monthv
                                          AND a.ShopID = b.ShopID
                                          AND a.KindID = b.KindID
                                          AND a.SeriesID = b.SeriesID
                                          AND a.MaterialProperty = b.MaterialProperty
               WHERE    ISNULL(a.MonthOrWeek, 0) = 0
                        AND ISNULL(a.AreaOrShop, 0) = 1
                        AND a.ExtrSource = '{UserInfo.UserExtrSource}'
                        AND a.CompanyID = '{param.CompanyID}'
                        AND a.Yearv = '{param.Yearv.Value}'
                        AND a.Monthv = '{param.Monthv.Value}'
                        AND a.ParentAreaID = '{param.ParentAreaID}'
                        AND a.KindID = '{param.KindID}'
                        AND a.SeriesID = '{param.SeriesID}'
                        AND a.MaterialProperty = '{param.MaterialProperty}'
             ) b WHERE b.RowNum = 1
                    AND a.ExtrSource = b.ExtrSource
                    AND a.CompanyID = b.CompanyID
                    AND a.Yearv = b.Yearv
                    AND a.Monthv = b.Monthv
                    AND a.ShopID = b.ShopID
                    AND a.KindID = b.KindID
                    AND a.SeriesID = b.SeriesID
                    AND a.MaterialProperty = b.MaterialProperty
                    AND a.ModelID = b.ModelID
                    AND ISNULL(MonthOrWeek, 0) = 0
                    AND ISNULL(AreaOrShop, 0) = 1";
                var cmdList = new List<CommandInfo>()
                {
                    new CommandInfo
                    {
                        CommandText = sqlPtStandAmountRate,
                    },
                    new CommandInfo
                    {
                        CommandText = sqlAutoStandAmountRate,
                    },
                    new CommandInfo
                    {
                        CommandText = sql,
                    }
                };
                Helper.ExecuteSqlTran(cmdList);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        /// <summary>
        /// 单店-周
        /// </summary>
        /// <param name="param"></param>
        /// Writer：梁家健
        /// Create Date：2017-05-22
        public void ModifyWeekShopPLModelProcess(PLModelProcessModel.NeceModel param)
        {
            try
            {
                var sqlPtStandAmountRate = $@"UPDATE  ngoods.ngoods_PL_Model_Process a
SET     PtStandAmountRate = CASE WHEN a.CalcStandAmountRate > b.PtStandAmountRate_p
                                 THEN b.PtStandAmountRate_p
                                 WHEN a.CalcStandAmountRate < b.PtStandAmountRate_m
                                 THEN b.PtStandAmountRate_m
                                 ELSE a.CalcStandAmountRate
                            END
FROM    ( SELECT DISTINCT
                            a.ExtrSource ,
                            a.CompanyID ,
                            a.ParentAreaID ,
                            b.ShopID ,
                            a.Yearv ,
                            a.Monthv ,
                            a.Weekv ,
                            a.KindID ,
                            a.SeriesID ,
                            a.MaterialProperty ,
                            a.ModelID ,
                            CASE WHEN a.PtStandAmountRate_p >= b.PtStandAmountRate_p
                                 THEN a.PtStandAmountRate_p
                                 ELSE b.PtStandAmountRate_p
                            END AS PtStandAmountRate_p ,
                            CASE WHEN a.PtStandAmountRate_m >= b.PtStandAmountRate_m
                                 THEN b.PtStandAmountRate_m
                                 ELSE a.PtStandAmountRate_m
                            END AS PtStandAmountRate_m
                     FROM   (
			--区域 周 
                              SELECT DISTINCT
                                        a.ExtrSource ,
                                        a.CompanyID ,
                                        a.ParentAreaID ,
                                        a.Yearv ,
                                        a.Monthv ,
                                        a.Weekv ,
                                        a.KindID ,
                                        a.SeriesID ,
                                        a.MaterialProperty ,
                                        a.ModelID ,
                                        CkPtStandAmountRate * ( 1
                                                              + ISNULL(b.AreaShopFloat,
                                                              0.2) ) AS PtStandAmountRate_p ,
                                        CkPtStandAmountRate * ( 1
                                                              - ISNULL(b.AreaShopFloat,
                                                              0.2) ) AS PtStandAmountRate_m
                              FROM      ngoods.ngoods_PL_Model_Process a
                                        LEFT JOIN ( SELECT  *
                                                    FROM    ngoods.ngoods_PL_Bas_ModelFloat
                                                    WHERE   ExtrSource = '{UserInfo.UserExtrSource}'
                                                            AND CompanyID = '{UserInfo.UserCompanyId}'
                                                  ) b ON a.ExtrSource = b.ExtrSource
                                                         AND a.CompanyID = b.CompanyID
                                                         AND a.MaterialProperty = b.MaterialProperty
                                                         AND a.ModelID = b.ModelID
                              WHERE     a.ExtrSource = '{UserInfo.UserExtrSource}'
                                        AND a.CompanyID = '{UserInfo.UserCompanyId}'
                                        AND ISNULL(a.MonthOrWeek, 0) = 1
                                        AND ISNULL(a.AreaOrShop, 0) = 0
                            ) a
                            INNER JOIN (
			--单店 月
                                         SELECT a.ExtrSource ,
                                                a.CompanyID ,
                                                a.ParentAreaID ,
                                                a.ShopID ,
                                                a.Yearv ,
                                                a.Monthv ,
                                                a.KindID ,
                                                a.SeriesID ,
                                                a.MaterialProperty ,
                                                a.ModelID ,
                                                CkPtStandAmountRate * ( 1
                                                              + ISNULL(b.MonthWeekFloat,
                                                              0.1) ) AS PtStandAmountRate_p ,
                                                CkPtStandAmountRate * ( 1
                                                              - ISNULL(b.MonthWeekFloat,
                                                              0.1) ) AS PtStandAmountRate_m
                                         FROM   ngoods.ngoods_PL_Model_Process a
                                                LEFT JOIN ( SELECT
                                                              *
                                                            FROM
                                                              ngoods.ngoods_PL_Bas_ModelFloat
                                                            WHERE
                                                              ExtrSource = '{UserInfo.UserExtrSource}'
                                                              AND CompanyID = '{UserInfo.UserCompanyId}'
                                                          ) b ON a.ExtrSource = b.ExtrSource
                                                              AND a.CompanyID = b.CompanyID
                                                              AND a.MaterialProperty = b.MaterialProperty
                                                              AND a.ModelID = b.ModelID
                                         WHERE  a.ExtrSource = '{UserInfo.UserExtrSource}'
                                                AND a.CompanyID = '{UserInfo.UserCompanyId}'
                                                AND ISNULL(a.MonthOrWeek, 0) = 0
                                                AND ISNULL(a.AreaOrShop, 0) = 1
                                         GROUP BY a.ExtrSource ,
                                                a.CompanyID ,
                                                a.ParentAreaID ,
                                                a.ShopID ,
                                                a.Yearv ,
                                                a.Monthv ,
                                                a.KindID ,
                                                a.SeriesID ,
                                                a.MaterialProperty ,
                                                a.ModelID ,
                                                CkPtStandAmountRate ,
                                                b.MonthWeekFloat
                                       ) b ON b.ExtrSource = a.ExtrSource
                                              AND b.CompanyID = a.CompanyID
                                              AND b.Yearv = a.Yearv
                                              AND b.Monthv = a.Monthv
                                              AND b.ParentAreaID = a.ParentAreaID
                                              AND b.KindID = a.KindID
                                              AND b.SeriesID = a.SeriesID
                                              AND b.MaterialProperty = a.MaterialProperty
                                              AND b.ModelID = a.ModelID
                     WHERE  a.ExtrSource = '{UserInfo.UserExtrSource}'
                            AND a.CompanyID = '{param.CompanyID}'
                            AND a.Yearv = '{param.Yearv.Value}'
                            AND a.Monthv = '{param.Monthv.Value}'
                            AND a.ParentAreaID = '{param.ParentAreaID}'
                            AND a.KindID = '{param.KindID}'
                            AND a.SeriesID = '{param.SeriesID}'
                            AND a.MaterialProperty = '{param.MaterialProperty}'
                   ) b WHERE b.ExtrSource = a.ExtrSource
                          AND b.CompanyID = a.CompanyID
                          AND b.Yearv = a.Yearv
                          AND b.Monthv = a.Monthv
                          AND b.Weekv = a.Weekv
                          AND b.ParentAreaID = a.ParentAreaID
                          AND b.ShopID = a.ShopID
                          AND b.KindID = a.KindID
                          AND b.SeriesID = a.SeriesID
                          AND b.MaterialProperty = a.MaterialProperty
                          AND b.ModelID = a.ModelID
                          AND ISNULL(MonthOrWeek, 0) = 1
                          AND ISNULL(AreaOrShop, 0) = 1";
                var sqlAutoStandAmountRate = $@"UPDATE  ngoods.ngoods_PL_Model_Process a
SET     AutoStandAmountRate = CASE WHEN m.PtStandAmountRate = 0 THEN 0
                                   ELSE a.PtStandAmountRate
                                        / m.PtStandAmountRate
                              END ,
        CkPtStandAmountRate = CASE WHEN m.PtStandAmountRate = 0 THEN 0
                                   ELSE a.PtStandAmountRate
                                        / m.PtStandAmountRate
                              END
FROM    ( SELECT   ExtrSource ,
                        CompanyID ,
                        Yearv ,
                        Monthv ,
                        Weekv ,
                        ParentAreaID ,
                        ShopID ,
                        KindID ,
                        SeriesID ,
                        MaterialProperty ,
                        SUM(PtStandAmountRate) AS PtStandAmountRate
               FROM     ngoods.ngoods_PL_Model_Process
               WHERE    ISNULL(MonthOrWeek, 0) = 1
                        AND ISNULL(AreaOrShop, 0) = 1
                        AND ExtrSource = '{UserInfo.UserExtrSource}'
                        AND CompanyID = '{UserInfo.UserCompanyId}'
                        AND Yearv = '{param.Yearv.Value}'
                        AND Monthv = '{param.Monthv.Value}'
                        AND ParentAreaID = '{param.ParentAreaID}'
                        AND KindID = '{param.KindID}'
                        AND SeriesID = '{param.SeriesID}'
                        AND MaterialProperty = '{param.MaterialProperty}'
               GROUP BY ExtrSource ,
                        CompanyID ,
                        Yearv ,
                        Monthv ,
                        Weekv ,
                        ParentAreaID ,
                        ShopID ,
                        KindID ,
                        SeriesID ,
                        MaterialProperty
             ) m WHERE m.ExtrSource = a.ExtrSource
                    AND m.CompanyID = a.CompanyID
                    AND m.Yearv = a.Yearv
                    AND m.Monthv = a.Monthv
                    AND m.Weekv = a.Weekv
                    AND m.ParentAreaID = a.ParentAreaID
                    AND m.ShopID = a.ShopID
                    AND m.SeriesID = a.SeriesID
                    AND m.KindID = a.KindID
                    AND m.MaterialProperty = a.MaterialProperty
                    AND ISNULL(MonthOrWeek, 0) = 1
                    AND ISNULL(AreaOrShop, 0) = 1";
                var sql = $@"UPDATE  ngoods.ngoods_PL_Model_Process a
SET     AutoStandAmountRate = a.AutoStandAmountRate - b.DiffPt ,
        CkPtStandAmountRate = a.AutoStandAmountRate - b.DiffPt
FROM    ( SELECT   a.ExtrSource ,
                        a.CompanyID ,
                        a.Yearv ,
                        a.Weekv ,
                        a.ParentAreaID ,
                        a.ShopID ,
                        a.KindID ,
                        a.SeriesID ,
                        a.MaterialProperty ,
                        a.ModelID ,
                        a.AutoStandAmountRate ,
                        b.AutoStandAmountRate - 1 AS DiffPt ,
                        ROW_NUMBER() OVER ( PARTITION BY a.Yearv, a.Weekv,
                                            a.ParentAreaID, a.ShopID, a.KindID,
                                            a.SeriesID, a.MaterialProperty ORDER BY a.AutoStandAmountRate DESC ) AS RowNum
               FROM     ngoods.ngoods_PL_Model_Process a
                        INNER JOIN ( SELECT ExtrSource ,
                                            CompanyID ,
                                            Yearv ,
                                            Weekv ,
                                            ParentAreaID ,
                                            ShopID ,
                                            KindID ,
                                            SeriesID ,
                                            MaterialProperty ,
                                            SUM(AutoStandAmountRate) AS AutoStandAmountRate
                                     FROM   ngoods.ngoods_PL_Model_Process
                                     WHERE  ISNULL(MonthOrWeek, 0) = 1
                                            AND ISNULL(AreaOrShop, 0) = 1
                                     GROUP BY ExtrSource ,
                                            CompanyID ,
                                            Yearv ,
                                            Weekv ,
                                            ParentAreaID ,
                                            ShopID ,
                                            KindID ,
                                            SeriesID ,
                                            MaterialProperty
                                     HAVING SUM(AutoStandAmountRate) <> 1
                                            AND SUM(AutoStandAmountRate) > 0
                                   ) b ON a.ExtrSource = b.ExtrSource
                                          AND a.CompanyID = b.CompanyID
                                          AND a.Yearv = b.Yearv
                                          AND a.Weekv = b.Weekv
                                          AND b.ParentAreaID = a.ParentAreaID
                                          AND b.ShopID = a.ShopID
                                          AND a.KindID = b.KindID
                                          AND a.SeriesID = b.SeriesID
                                          AND a.MaterialProperty = b.MaterialProperty
               WHERE    ISNULL(a.MonthOrWeek, 0) = 1
                        AND ISNULL(a.AreaOrShop, 0) = 1
                        AND a.ExtrSource = '{UserInfo.UserExtrSource}'
                        AND a.CompanyID = '{UserInfo.UserCompanyId}'
                        AND a.Yearv = '{param.Yearv.Value}'
                        AND a.Monthv = '{param.Monthv.Value}'
                        AND a.ParentAreaID = '{param.ParentAreaID}'
                        AND a.KindID = '{param.KindID}'
                        AND a.SeriesID = '{param.SeriesID}'
                        AND a.MaterialProperty = '{param.MaterialProperty}'
             ) b WHERE b.RowNum = 1
                    AND a.ExtrSource = b.ExtrSource
                    AND a.CompanyID = b.CompanyID
                    AND a.ParentAreaID = b.ParentAreaID
                    AND a.ShopID = b.ShopID
                    AND a.Yearv = b.Yearv
                    AND a.Weekv = b.Weekv
                    AND a.KindID = b.KindID
                    AND a.SeriesID = b.SeriesID
                    AND a.MaterialProperty = b.MaterialProperty
                    AND a.ModelID = b.ModelID
                    AND ISNULL(MonthOrWeek, 0) = 1
                    AND ISNULL(AreaOrShop, 0) = 1";
                var cmdList = new List<CommandInfo>()
                {
                    new CommandInfo
                    {
                        CommandText = sqlPtStandAmountRate,
                    },
                    new CommandInfo
                    {
                        CommandText = sqlAutoStandAmountRate,
                    },
                    new CommandInfo
                    {
                        CommandText = sql,
                    }
                };
                Helper.ExecuteSqlTran(cmdList);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }
    }
}
